package org.jeecg.modules.system.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.jeecg.modules.system.entity.EqPowerEntity;
import org.jeecg.modules.system.entity.SitePowerEntity;

import java.util.List;
import java.util.Map;

public interface JournalMapper extends BaseMapper {

    @Select(" select a.device_id,a.measure_time,ROUND(CONVERT(a.power_consumption , CHAR),3) power_consumption,ROUND(CONVERT(a.power_generation , CHAR),3) power_generation,CONVERT(a.apparent_power , CHAR) apparent_power from biz_meter_data_use a " +
            "where a.device_id=#{deviceId} and a.measure_time between #{startDay} and #{endDay} ")
    List<Map<?,?>> getMinMeterReport(@Param("startDay") String startDay,@Param("endDay") String endDay,@Param("deviceId") String deviceId);
    @Select(" select a.device_id,a.measure_time,ROUND(CONVERT(a.power_consumption , CHAR),3) power_consumption,ROUND(CONVERT(a.power_generation , CHAR),3) power_generation from biz_meter_data_hour a " +
            "where a.device_id=#{deviceId} and a.measure_time between #{startDay} and #{endDay}")
    List<Map<?,?>> getHourMeterReport(@Param("startDay") String startDay,@Param("endDay") String endDay,@Param("deviceId") String deviceId);

    @Select(" select a.device_id,a.measure_time,ROUND(CONVERT(a.power_consumption , CHAR),3) power_consumption,ROUND(CONVERT(a.power_generation , CHAR),3) power_generation from biz_meter_data_day a " +
            "where a.device_id=#{deviceId} and a.measure_time between #{startDay} and #{endDay} order by a.measure_time asc ")
    List<Map<?,?>> getDayMeterReport(@Param("startDay") String startDay,@Param("endDay") String endDay,@Param("deviceId") String deviceId);

    @Select(" select a.device_id,a.measure_time,ROUND(CONVERT(a.power_consumption , CHAR),3) power_consumption,ROUND(CONVERT(a.power_generation , CHAR),3) power_generation from biz_meter_data_month a " +
            "where a.device_id=#{deviceId} and a.measure_time between #{startDay} and #{endDay} order by a.measure_time asc ")
    List<Map<?,?>> getMonthMeterReport(@Param("startDay") String startDay,@Param("endDay") String endDay,@Param("deviceId") String deviceId);

    @Select(" select a.device_id,a.measure_time,ROUND(CONVERT(a.power_consumption , CHAR),3) power_consumption,ROUND(CONVERT(a.power_generation , CHAR),3) power_generation from biz_meter_data_year a " +
            "where a.device_id=#{deviceId} and a.measure_time between #{startDay} and #{endDay} order by a.measure_time asc ")
    List<Map<?,?>> getYearMeterReport(@Param("startDay") String startDay,@Param("endDay") String endDay,@Param("deviceId") String deviceId);

    @Select("<script>" +
            "select DATE_FORMAT(a.measure_time, '%Y-%m-%d %H:%i:00') measure_time,ROUND(CONVERT(sum(a.power_consumption) , CHAR),3) power_consumption,ROUND(CONVERT(sum(if(b.use_in_count=0,power_generation,0)) , CHAR),3) power_generation from biz_meter_data_use a,biz_equipment b " +
            "where a.device_id =b.eq_id and b.eq_type in (${eqType}) <when test='siteId !=null'> and b.site_id = #{siteId} </when> and a.measure_time between #{startDay} and #{endDay} group by DATE_FORMAT(measure_time, '%Y-%m-%d %H:%i:00') order by DATE_FORMAT(measure_time, '%Y-%m-%d %H:%i:00') asc" +
            "</script>")
    List<SitePowerEntity> getMinSiteReport(@Param("startDay") String startDay, @Param("endDay") String endDay, @Param("siteId") String siteId,@Param("eqType") String eqType);
    @Select("<script>" +
            "select a.measure_time,ROUND(CONVERT(sum(a.power_consumption) , CHAR),3) power_consumption,ROUND(CONVERT(sum(if(b.use_in_count=0,power_generation,0)) , CHAR),3) power_generation from biz_meter_data_hour a,biz_equipment b " +
            "where a.device_id =b.eq_id and b.eq_type in (${eqType}) <when test='siteId !=null'> and b.site_id = #{siteId} </when> and a.measure_time between #{startDay} and #{endDay} group by measure_time order by measure_time asc" +
            "</script>")
    List<SitePowerEntity> getHourSiteReport(@Param("startDay") String startDay, @Param("endDay") String endDay, @Param("siteId") String siteId,@Param("eqType") String eqType);

    @Select("<script>" +
            "select DATE_FORMAT(a.measure_time, '%Y-%m-%d') measure_time,ROUND(CONVERT(sum(a.power_consumption) , CHAR),3) power_consumption,ROUND(CONVERT(sum(if(b.use_in_count=0,power_generation,0)) , CHAR),3) power_generation from biz_meter_data_day a,biz_equipment b " +
            "where a.device_id =b.eq_id and b.eq_type in (${eqType}) <when test='siteId !=null'> and b.site_id = #{siteId} </when> and a.measure_time between #{startDay} and #{endDay} group by measure_time order by measure_time asc" +
            "</script>")
    List<SitePowerEntity> getDaySiteReport(@Param("startDay") String startDay,@Param("endDay") String endDay,@Param("siteId") String siteId,@Param("eqType") String eqType);

    @Select("<script> " +
            "select DATE_FORMAT(a.measure_time, '%Y-%m') measure_time,ROUND(CONVERT(sum(a.power_consumption) , CHAR),3) power_consumption,ROUND(CONVERT(sum(if(b.use_in_count=0,power_generation,0)) , CHAR),3) power_generation from biz_meter_data_month a,biz_equipment b " +
            "where a.device_id =b.eq_id and b.eq_type in (${eqType}) <when test='siteId !=null'> and b.site_id = #{siteId} </when> and a.measure_time between #{startDay} and #{endDay} group by measure_time order by measure_time asc" +
            "</script>")
    List<SitePowerEntity> getMonthSiteReport(@Param("startDay") String startDay,@Param("endDay") String endDay,@Param("siteId") String siteId,@Param("eqType") String eqType);

    @Select("<script>" +
            " select DATE_FORMAT(a.measure_time, '%Y'),ROUND(CONVERT(sum(a.power_consumption) , CHAR),3) power_consumption,ROUND(CONVERT(sum(if(b.use_in_count=0,power_generation,0)) , CHAR),3) power_generation from biz_meter_data_year a,biz_equipment b " +
            "where a.device_id =b.eq_id and b.eq_type in (${eqType}) <when test='siteId !=null'> and b.site_id = #{siteId} </when>  and a.measure_time between #{startDay} and #{endDay} group by measure_time order by measure_time asc" +
            "</script>")
    List<SitePowerEntity> getYearSiteReport(@Param("startDay") String startDay,@Param("endDay") String endDay,@Param("siteId") String siteId,@Param("eqType") String eqType);

    @Select("select c.item_text, count(1) count " +
            "from biz_equipment a , sys_dict b ,sys_dict_item c " +
            "where b.dict_code = 'dict_eq_type' and b.id = c.dict_id and c.status=1 and a.eq_type = c.item_value and a.status=1 and a.site_id = #{siteId} " +
            "group by c.item_text")
    List<Map<?,?>> getEqTypeCount(@Param("siteId") String siteId);

    @Select("select a.device_id eqId,b.eq_name,IFNULL(ROUND(sum(power_consumption),3),0) power_consumption,IFNULL(ROUND(sum(power_generation),3),0) power_generation " +
            "from biz_meter_data_day a left join biz_equipment b on a.device_id = b.eq_id " +
            "where measure_time between #{startDay} and #{endDay} " +
            "and a.device_id = #{eqId}")
    List<EqPowerEntity> getEqPowerTotal(@Param("startDay") String startDay, @Param("endDay") String endDay, @Param("eqId") String eqId);

    @Select("select b.eq_Id,b.eq_name, IFNULL(ROUND(sum(power_consumption),3),0) power_consumption,IFNULL(ROUND(sum(power_generation),3),0) power_generation " +
            "from biz_meter_data_day a, biz_equipment b " +
            "where measure_time between #{startDay} and #{endDay} " +
            "and a.device_id =b.eq_id and b.eq_type not in (1) and b.site_id = #{siteId} " +
            "group by device_id order by ${sortKey} ${sortType}")
    List<EqPowerEntity> getEqPowerTotalBySite(@Param("startDay") String startDay,@Param("endDay") String endDay,@Param("siteId") String siteId,@Param("sortKey") String sortKey,@Param("sortType") String sortType);

    @Select("select ${siteId} siteId,IFNULL(ROUND(sum(power_consumption),3),0) power_consumption,IFNULL(ROUND(sum(if(b.use_in_count=0,power_generation,0)),3),0) power_generation  " +
            "from biz_meter_data_day a ,biz_equipment b " +
            "where a.measure_time between #{startDay} and #{endDay} " +
            "and a.device_id = b.eq_id  and b.site_id=#{siteId} and b.eq_type=#{eqType} ")
    List<Map<String,Object>> getSitePowerTotal(@Param("startDay") String startDay,@Param("endDay") String endDay,@Param("siteId") String siteId,@Param("eqType") String eqType);

    @Select("<script>" +
            "select DATE_FORMAT(begin_time, '%Y-%m-%d %H') alarm_date,count(1) count from biz_alarm where begin_time between #{startDay} and #{endDay} <when test='eqId !=null'> and eq_id = #{eqId} </when> group by DATE_FORMAT(begin_time, '%Y-%m-%d %H')" +
            "</script>")
    List<Map<?,?>> getHourAlarmReport(@Param("startDay") String startDay,@Param("endDay") String endDay,@Param("eqId") String eqId);

    @Select("<script>" +
            "select DATE_FORMAT(begin_time, '%Y-%m-%d') alarm_date,count(1) count from biz_alarm where begin_time between #{startDay} and #{endDay} <when test='eqId !=null'> and eq_id = #{eqId} </when> group by DATE_FORMAT(begin_time, '%Y-%m-%d')" +
            "</script>")
    List<Map<?,?>> getDayAlarmReport(@Param("startDay") String startDay,@Param("endDay") String endDay,@Param("eqId") String eqId);

    @Select("<script>" +
            "select DATE_FORMAT(begin_time, '%Y-%m') alarm_date,count(1) count from biz_alarm where begin_time between #{startDay} and #{endDay} <when test='eqId !=null'> and eq_id = #{eqId} </when> group by DATE_FORMAT(begin_time, '%Y-%m')" +
            "</script>")
    List<Map<?,?>> getMonthAlarmReport(@Param("startDay") String startDay,@Param("endDay") String endDay,@Param("eqId") String eqId);
    @Select("<script>" +
            "select DATE_FORMAT(begin_time, '%Y') alarm_date,count(1) count from biz_alarm where begin_time between #{startDay} and #{endDay} <when test='eqId !=null'> and eq_id = #{eqId} </when> group by DATE_FORMAT(begin_time, '%Y')" +
            "</script>")
    List<Map<?,?>> getYearAlarmReport(@Param("startDay") String startDay,@Param("endDay") String endDay,@Param("eqId") String eqId);
}
